-- Build table to hold values during conversion
CREATE TABLE #Space ( MachineId                   int      NOT NULL, 
                      MachineDriveVolumeId        int      NOT NULL, 
                      CaptureDt                   datetime NOT NULL, 
                      FreeSpaceOutsideTolerance   tinyint      NULL, 
                      FreeSpaceAlertGeneratedFlag tinyint      NULL )
GO

-- Copy the values
INSERT #Space
     ( MachineId,
       MachineDriveVolumeId,
       CaptureDt,
       FreeSpaceOutsideTolerance,
       FreeSpaceAlertGeneratedFlag )
SELECT MachineId,
       MachineDriveVolumeId,
       CaptureDt,
       FreeSpaceOutsideTolerance,
       FreeSpaceAlertGeneratedFlag
FROM   dbo.MachineDriveVolumeSpace
WHERE  FreeSpaceOutsideTolerance   IS NOT NULL
OR     FreeSpaceAlertGeneratedFlag IS NOT NULL
GO

-- Add new columns
ALTER TABLE dbo.MachineDriveVolumeSpace
     ADD
          [VolumeFreeSpacePCT]  AS (CONVERT([decimal](7,4),(CONVERT([decimal](38,5),[VolumeFreeSpaceBytes],0)/CONVERT([decimal](38,5),[VolumeCapacityBytes],0))*(100),0)) PERSISTED,
	     [VolumeUsedSpacePCT]  AS (CONVERT([decimal](7,4),(CONVERT([decimal](38,5),[VolumeCapacityBytes]-[VolumeFreeSpaceBytes],0)/CONVERT([decimal](38,5),[VolumeCapacityBytes],0))*(100),0)) PERSISTED
GO

-- Remove End columns
ALTER TABLE dbo.MachineDriveVolumeSpace
     DROP COLUMN [FreeSpaceOutsideTolerance], [FreeSpaceAlertGeneratedFlag]
GO

-- Re-Add End columns
ALTER TABLE dbo.MachineDriveVolumeSpace
     ADD
    	     [FreeSpaceOutsideTolerance] [tinyint] NULL,
	     [FreeSpaceAlertGeneratedFlag] [tinyint] NULL
GO

-- Reset the saved values
UPDATE dbo.MachineDriveVolumeSpace
SET    FreeSpaceOutsideTolerance   = ts.FreeSpaceOutsideTolerance,
       FreeSpaceAlertGeneratedFlag = ts.FreeSpaceAlertGeneratedFlag
FROM   dbo.MachineDriveVolumeSpace s INNER JOIN #Space ts
          ON s.MachineId            = ts.MachineId
         AND s.MachineDriveVolumeId = ts.MachineDriveVolumeId
         AND s.CaptureDt            = ts.CaptureDt
GO

DROP TABLE #Space
GO
